﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using Wilson.ORMapper;
using System.Collections;

/// <summary>
/// Summary description for ImportPrint
/// </summary>
public class DocPrint
{
    public DocPrint()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public static DataSet dsInvoice(string invN)
    {
        DataSet set = new DataSet();
        DataTable mast = new DataTable("Mast");
        mast.Columns.Add("DocNo");
        mast.Columns.Add("DocDate");
        mast.Columns.Add("BranchName");
        mast.Columns.Add("Business");
        mast.Columns.Add("Address");
        mast.Columns.Add("Phone");
        mast.Columns.Add("Fax");
        mast.Columns.Add("Technician");

        mast.Columns.Add("Cust");
        mast.Columns.Add("CustContact");
        mast.Columns.Add("CustAddress");
        mast.Columns.Add("CustPhone");
        mast.Columns.Add("CustFax");
        mast.Columns.Add("CustTerm");

        mast.Columns.Add("Tax");
        mast.Columns.Add("SubAmt");
        mast.Columns.Add("TaxAmt");
        mast.Columns.Add("TotAmt");

        DataTable det = new DataTable("Det");
        det.Columns.Add("DocNo");
        det.Columns.Add("Item");
        det.Columns.Add("VehicleNo");
        det.Columns.Add("GroupId");
        det.Columns.Add("Year");
        det.Columns.Add("Make");
        det.Columns.Add("Model");
        det.Columns.Add("Color");
        det.Columns.Add("Vin");
        det.Columns.Add("Des");
        det.Columns.Add("Amt");
        string sql = "SELECT DocNo, DocDate, JobOrder, BranchId, CustId, Term,TotAmt FROM OpInvoice where DocType='Invoice' and DocNo='" + invN + "'";
        DataTable tab1 = Touchmax.Manager.ORManager.GetDataSet(sql).Tables[0];
        if (tab1.Rows.Count == 1)
        {

            string sql_Det = "SELECT DocLineNo, ItemId, ChgCode, Remark, TotAmt FROM OpInvoiceDet Where DocNo='" + invN + "' order by docLineNo";
            decimal totAmt = 0;
            DataTable tab_Det = Touchmax.Manager.ORManager.GetDataSet(sql_Det).Tables[0];
            int item = 1;
            int groupId =0;
            string lastVehicle = "";
            int cnt = 21;
            for (int i = 0; i < tab_Det.Rows.Count; i++)
            {
                DataRow rowDet = det.NewRow();
                rowDet["Item"] = item;
                rowDet["DocNo"] = invN;
                string vehicleId = SafeValue.SafeString(tab_Det.Rows[i]["ItemId"], "");
                if (lastVehicle != vehicleId)
                {
                    groupId++;
                    lastVehicle = vehicleId;
                    rowDet["GroupId"] = groupId;
                }else
                    rowDet["GroupId"] = groupId;

                sql_Det = "SELECT VehicleNo, Color1,VehicleType, MadeBy, VehicleModel, Year, Engine, Vin, Isurance, IsuranceCompany, Remark, JobOrder FROM OpVehicle where SequenceId='" + vehicleId + "' order by vehicleNo";
                DataTable tab_Vehicle = Touchmax.Manager.ORManager.GetDataSet(sql_Det).Tables[0];
                if (tab_Vehicle.Rows.Count > 0)
                {
                    rowDet["VehicleNo"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["VehicleNo"], "");
                    rowDet["Year"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["Year"], "");
                    rowDet["Make"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["VehicleType"], "");
                    rowDet["Model"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["MadeBy"], "");
                    rowDet["Color"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["Color1"], "");
                    rowDet["Vin"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["Vin"], "");
                }
                rowDet["Des"] = SafeValue.SafeString(tab_Det.Rows[i]["Remark"], "");
                rowDet["Amt"] = SafeValue.SafeDecimal(tab_Det.Rows[i]["TotAmt"], 0).ToString("$ 0.00");
                totAmt += SafeValue.SafeDecimal(tab_Det.Rows[i]["TotAmt"], 0);
                det.Rows.Add(rowDet);
                item++;
            }
            for (int i = 0; i < cnt - item; i++)
            {
                DataRow rowDet = det.NewRow();
                rowDet["Item"] = item;
                rowDet["DocNo"] = invN;
                rowDet["GroupId"] = groupId;
   
                det.Rows.Add(rowDet);
            }




            DataRow rowMast = mast.NewRow();
            rowMast["DocNo"] = invN;
            rowMast["DocDate"] = SafeValue.SafeDate(tab1.Rows[0]["DocDate"], DateTime.Today).ToString("dd/MM/yyyy");
            rowMast["CustTerm"] = SafeValue.SafeString(tab1.Rows[0]["Term"], "");
            string branchId = SafeValue.SafeString(tab1.Rows[0]["BranchId"], "");
            sql = "SELECT CustId, Code, Name, Address, Tel1, Tel2, Fax1, Fax2, Email1, Email2, Contact1, Contact2 FROM BoBranch where custId='" + branchId + "'";
            DataTable tab_Branch = Touchmax.Manager.ORManager.GetDataSet(sql).Tables[0];
            if (tab_Branch.Rows.Count == 1)
            {
                rowMast["BranchName"] = SafeValue.SafeString(tab_Branch.Rows[0]["Name"], "");
                rowMast["Address"] = SafeValue.SafeString(tab_Branch.Rows[0]["Address"], "");
                rowMast["Phone"] = SafeValue.SafeString(tab_Branch.Rows[0]["Tel1"], "");
                rowMast["Fax"] = SafeValue.SafeString(tab_Branch.Rows[0]["Fax1"], "");
            }
            sql = "select Tehnician from OpOrder where JobNo='"+tab1.Rows[0]["JobOrder"]+"'";
                rowMast["Technician"] = SafeValue.SafeString(Touchmax.Manager.ORManager.ExecuteScalar(sql), "");
            string custId = SafeValue.SafeString(tab1.Rows[0]["CustId"], "");
            sql = "SELECT CustId, Code, Name, GroupId, Address, Tel1, Tel2, Fax1, Fax2, Email1, Email2, Contact1, Contact2 FROM BoClient where custId='" + custId + "'";
            DataTable tab_Cust = Touchmax.Manager.ORManager.GetDataSet(sql).Tables[0];
            if (tab_Cust.Rows.Count == 1)
            {
                rowMast["Cust"] = SafeValue.SafeString(tab_Cust.Rows[0]["Name"], "");
                rowMast["CustContact"] = SafeValue.SafeString(tab_Cust.Rows[0]["Contact1"], "");
                rowMast["CustAddress"] = SafeValue.SafeString(tab_Cust.Rows[0]["Address"], "");
                rowMast["CustPhone"] = SafeValue.SafeString(tab_Cust.Rows[0]["Tel1"], "");
                rowMast["CustFax"] = SafeValue.SafeString(tab_Cust.Rows[0]["Fax1"], "");

                rowMast["Tax"] = "0";
                rowMast["SubAmt"] = totAmt.ToString("$ 0.00");
                rowMast["TaxAmt"] = "$ 0.00";
                rowMast["TotAmt"] = totAmt.ToString("$ 0.00");
            }
                mast.Rows.Add(rowMast);
        }

        set.Tables.Add(mast);
        set.Tables.Add(det);
        set.Relations.Add("", mast.Columns["DocNo"], det.Columns["DocNo"]);
        return set;
    }

    public static DataSet dsEstimate(string jobNo)
    {
        DataSet set = new DataSet();
        DataTable mast = new DataTable("Mast");
        mast.Columns.Add("DocNo");
        mast.Columns.Add("DocDate");
        mast.Columns.Add("BranchName");
        mast.Columns.Add("Business");
        mast.Columns.Add("Address");
        mast.Columns.Add("Phone");
        mast.Columns.Add("Fax");
        mast.Columns.Add("Technician");

        mast.Columns.Add("Cust");
        mast.Columns.Add("CustContact");
        mast.Columns.Add("CustAddress");
        mast.Columns.Add("CustPhone");
        mast.Columns.Add("CustFax");
        mast.Columns.Add("CustTerm");

        mast.Columns.Add("Tax");
        mast.Columns.Add("SubAmt");
        mast.Columns.Add("TaxAmt");
        mast.Columns.Add("TotAmt");

        DataTable det = new DataTable("Det");
        det.Columns.Add("DocNo");
        det.Columns.Add("Item");
        det.Columns.Add("VehicleNo");
        det.Columns.Add("GroupId");
        det.Columns.Add("Year");
        det.Columns.Add("Make");
        det.Columns.Add("Model");
        det.Columns.Add("Color");
        det.Columns.Add("Vin");
        det.Columns.Add("Des");
        det.Columns.Add("Amt");
        string sql = "SELECT JobDate,  CustId,CustContact,Tehnician  FROM OpOrder where JobNo='" + jobNo + "'";
        DataTable tab1 = Touchmax.Manager.ORManager.GetDataSet(sql).Tables[0];
        if (tab1.Rows.Count == 1)
        {

            string sql_Det = "SELECT DocLineNo, ItemId, ChgCode, Remark, TotAmt FROM OpInvoiceDet Where DocType='Estimate' and JobOrder='" + jobNo+ "' order by docLineNo";
            decimal totAmt = 0;
            DataTable tab_Det = Touchmax.Manager.ORManager.GetDataSet(sql_Det).Tables[0];
            int item = 1;
            int groupId = 0;
            string lastVehicle = "";
            int cnt = 21;
            for (int i = 0; i < tab_Det.Rows.Count; i++)
            {
                DataRow rowDet = det.NewRow();
                rowDet["Item"] = item;
                rowDet["DocNo"] = jobNo;
                string vehicleId = SafeValue.SafeString(tab_Det.Rows[i]["ItemId"], "");
                if (lastVehicle != vehicleId)
                {
                    groupId++;
                    lastVehicle = vehicleId;
                    rowDet["GroupId"] = groupId;
                }
                else
                    rowDet["GroupId"] = groupId;

                sql_Det = "SELECT VehicleNo, Color1,VehicleType, MadeBy, VehicleModel, Year, Engine, Vin, Isurance, IsuranceCompany, Remark, JobOrder FROM OpVehicle where SequenceId='" + vehicleId + "' order by vehicleNo";
                DataTable tab_Vehicle = Touchmax.Manager.ORManager.GetDataSet(sql_Det).Tables[0];
                if (tab_Vehicle.Rows.Count > 0)
                {
                    rowDet["VehicleNo"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["VehicleNo"], "");
                    rowDet["Year"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["Year"], "");
                    rowDet["Make"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["VehicleType"], "");
                    rowDet["Model"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["VehicleModel"], "");
                    rowDet["Color"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["Color1"], "");
                    rowDet["Vin"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["Vin"], "");
                }
                rowDet["Des"] = SafeValue.SafeString(tab_Det.Rows[i]["Remark"], "");
                rowDet["Amt"] = SafeValue.SafeDecimal(tab_Det.Rows[i]["TotAmt"], 0).ToString("$ 0.00");
                totAmt += SafeValue.SafeDecimal(tab_Det.Rows[i]["TotAmt"], 0);
                det.Rows.Add(rowDet);
                item++;
            }
            for (int i = 0; i < cnt - item; i++)
            {
                DataRow rowDet = det.NewRow();
                rowDet["Item"] = item;
                rowDet["DocNo"] = jobNo;
                rowDet["GroupId"] = groupId;
                //    rowDet["VehicleNo"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["VehicleNo"], "");
                //    rowDet["Year"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["Year"], "");
                //    rowDet["Make"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["VehicleType"], "");
                //    rowDet["Model"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["MadeBy"], "");
                //    rowDet["Color"] = "";// SafeValue.SafeString(tab_Vehicle.Rows[0]["MadeBy"], "");
                //    rowDet["Vin"] = SafeValue.SafeString(tab_Vehicle.Rows[0]["Vin"], "");
                //}
                //rowDet["Des"] = SafeValue.SafeString(tab_Det.Rows[i]["Remark"], "");
                //rowDet["Amt"] = SafeValue.SafeDecimal(tab_Det.Rows[i]["TotAmt"], 0).ToString("0.00");
                //totAmt += SafeValue.SafeDecimal(tab_Det.Rows[i]["TotAmt"], 0);
                det.Rows.Add(rowDet);
                //item++;
            }














            DataRow rowMast = mast.NewRow();
            rowMast["DocNo"] = jobNo;
            rowMast["DocDate"] = SafeValue.SafeDate(tab1.Rows[0]["JobDate"], DateTime.Today).ToString("dd/MM/yyyy");
            //rowMast["CustTerm"] = SafeValue.SafeString(tab1.Rows[0]["Term"], "");
            string branchId = "1001";// SafeValue.SafeString(tab1.Rows[0]["BranchId"], "");
            sql = "SELECT CustId, Code, Name, Address, Tel1, Tel2, Fax1, Fax2, Email1, Email2, Contact1, Contact2 FROM XXBranch where custId='" + branchId + "'";
            DataTable tab_Branch = Touchmax.Manager.ORManager.GetDataSet(sql).Tables[0];
            if (tab_Branch.Rows.Count == 1)
            {
                rowMast["BranchName"] = SafeValue.SafeString(tab_Branch.Rows[0]["Name"], "");
                rowMast["Address"] = SafeValue.SafeString(tab_Branch.Rows[0]["Address"], "");
                rowMast["Phone"] = SafeValue.SafeString(tab_Branch.Rows[0]["Tel1"], "");
                rowMast["Fax"] = SafeValue.SafeString(tab_Branch.Rows[0]["Fax1"], "");
             }

             rowMast["Technician"] = SafeValue.SafeString(tab1.Rows[0]["Tehnician"], "");
            string custId = SafeValue.SafeString(tab1.Rows[0]["CustId"], "");
            sql = "SELECT CustId, Code, Name, GroupId, Address, Tel1, Tel2, Fax1, Fax2, Email1, Email2, Contact1, Contact2 FROM BoClient where custId='" + custId + "'";
            DataTable tab_Cust = Touchmax.Manager.ORManager.GetDataSet(sql).Tables[0];
            if (tab_Cust.Rows.Count == 1)
            {
                rowMast["Cust"] = SafeValue.SafeString(tab_Cust.Rows[0]["Name"], "");
                rowMast["CustContact"] = SafeValue.SafeString(tab_Cust.Rows[0]["Contact1"], "");
                rowMast["CustAddress"] = SafeValue.SafeString(tab_Cust.Rows[0]["Address"], "");
                rowMast["CustPhone"] = SafeValue.SafeString(tab_Cust.Rows[0]["Tel1"], "");
                rowMast["CustFax"] = SafeValue.SafeString(tab_Cust.Rows[0]["Fax1"], "");

                rowMast["Tax"] = "0";
                rowMast["SubAmt"] = totAmt.ToString("0.00");
                rowMast["TaxAmt"] = "0.00";
                rowMast["TotAmt"] = totAmt.ToString("0.00");
            }
                mast.Rows.Add(rowMast);
        }

        set.Tables.Add(mast);
        set.Tables.Add(det);
        set.Relations.Add("", mast.Columns["DocNo"], det.Columns["DocNo"]);
        return set;
    }

}
